package com.sen.app.timemail.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class ExcelReaderUtils {
	/**
	 * 工作薄，一个excle文件
	 */
	private HSSFWorkbook wb;

	/**
	 * excle文件中的多个sheet
	 */
	private HSSFSheet sheet;

	/**
	 * 代表了表的第一行，也就是列名
	 */
	private HSSFRow row;

	/**
	 * sheet工作表个数
	 */
	private int sheetNum = 0;

	/**
	 * sheet工作表的行数
	 */
	private int rowNum = 0;

	/**
	 * 文件输入流
	 */
	private FileInputStream fis = null;

	/**
	 * excel文件对象
	 */
	private File file = null;

	public ExcelReaderUtils(File file) {
		this.file = file;
	}

	public void setRowNum(int rowNum) {
		this.rowNum = rowNum;
	}

	public void setSheetNum(int sheetNum) {
		this.sheetNum = sheetNum;
	}

	public void setFile(File file) {
		this.file = file;
	}

	/**
	 * 读取excel文件获得HSSFWorkbook对象
	 */
	public void open() throws IOException {
		fis = new FileInputStream(file);
		wb = new HSSFWorkbook(new POIFSFileSystem(fis));
		fis.close();
	}

	/**
	 * 返回sheet表数目
	 * 
	 * @return int
	 */
	public int getSheetCount() {
		int sheetCount = -1;
		sheetCount = wb.getNumberOfSheets();
		return sheetCount;
	}

	/**
	 * sheetNum下的记录行数
	 * 
	 * @return int
	 */
	public int getRowCount() {
		int rowCount = -1;
		if (wb != null) {
			HSSFSheet sheet = wb.getSheetAt(this.sheetNum);
			rowCount = sheet.getLastRowNum();
		}
		return rowCount;
	}

	/**
	 * 读取指定sheetNum的rowCount
	 * 
	 * @param sheetNum
	 * @return int
	 */
	public int getRowCount(int sheetNum) {
		int rowCount = -1;
		if (wb != null) {
			HSSFSheet sheet = wb.getSheetAt(sheetNum);
			rowCount = sheet.getLastRowNum();
		}
		return rowCount;
	}

	/**
	 * 得到指定行的内容
	 * 
	 * @param lineNum
	 * @return String[]
	 */
	public String[] readExcelLine(int lineNum) {
		return readExcelLine(this.sheetNum, lineNum);
	}

	/**
	 * 指定工作表和行数的内容
	 * 
	 * @param sheetNum
	 * @param lineNum
	 * @return String[]
	 */
	public String[] readExcelLine(int sheetNum, int lineNum) {
		if (sheetNum < 0 || lineNum < 0)
			return null;
		String[] strExcelLine = null;
		try {
			sheet = wb.getSheetAt(sheetNum);
			row = sheet.getRow(lineNum);

			int cellCount = row.getLastCellNum();
			strExcelLine = new String[cellCount];
			for (int i = 0; i < cellCount; i++) {
				strExcelLine[i] = readStringExcelCell(lineNum, i);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return strExcelLine;
	}

	/**
	 * 读取指定列的内容
	 * 
	 * @param cellNum
	 * @return String
	 */
	public String readStringExcelCell(int cellNum) {
		return readStringExcelCell(this.rowNum, cellNum);
	}

	/**
	 * 指定行和列编号的内容
	 * 
	 * @param rowNum
	 * @param cellNum
	 * @return String
	 */
	public String readStringExcelCell(int rowNum, int cellNum) {
		return readStringExcelCell(this.sheetNum, rowNum, cellNum);
	}

	/**
	 * 指定工作表、行、列下的内容
	 * 
	 * @param sheetNum
	 * @param rowNum
	 * @param cellNum
	 * @return String
	 */
	public String readStringExcelCell(int sheetNum, int rowNum, int cellNum) {
		if (sheetNum < 0 || rowNum < 0)
			return "";
		String strExcelCell = "";
		try {
			sheet = wb.getSheetAt(sheetNum);
			row = sheet.getRow(rowNum);
			HSSFCell cell = row.getCell(cellNum);
			if (cell != null) {
				switch (cell.getCellType()) {
					case HSSFCell.CELL_TYPE_FORMULA :
						strExcelCell = cell.getCellFormula();
						break;
					case HSSFCell.CELL_TYPE_NUMERIC : 
						strExcelCell = String.valueOf(cell.getNumericCellValue());
						break;
					case HSSFCell.CELL_TYPE_STRING :
						strExcelCell = cell.getStringCellValue();
						break;
					case HSSFCell.CELL_TYPE_BLANK :
						strExcelCell = cell.getStringCellValue();
						break;
					case HSSFCell.CELL_TYPE_BOOLEAN :
						boolean tempValue = cell.getBooleanCellValue();
						strExcelCell = String.valueOf(tempValue);
						break;
					default:
						break;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return strExcelCell;
	}

	public static void main(String args[]) {
		File file = new File("D:/temp/emailTable.xls");
		ExcelReaderUtils readExcel = new ExcelReaderUtils(file);
		try {
			readExcel.open();
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		// 设置从第一个sheet开始读取
		readExcel.setSheetNum(0);
		
		// 总行数
		int count = readExcel.getRowCount();
		System.out.println("总行数：" + count);
		for (int i = 0; i <= count; i++) {
			String[] rows = readExcel.readExcelLine(i);
			for (int j = 0; j < rows.length; j++) {
			    System.out.print(rows[j] + "|");
			}
			System.out.print("\n");
		}
	}
}
